﻿CREATE PROCEDURE [dbo].[books_get_best]
    @userId int = null
AS
SELECT
    BookId,
    [DateOfPublish],
    [ShortContent],
    [Title],
    [Avatar],
    [FileName],
    (SELECT DISTINCT CAST(BooksByAuthor.AuthorId as nvarchar) + ',' AS 'data()'
                     FROM   BooksByAuthor
                     WHERE  BooksByAuthor.BookId = Books.BookId FOR xml path('')) AS authors,
     Rating = cast((SELECT Sum(Value) / Count(1)   From Ratings Where (Ratings.BookId=Books.BookId)) as nvarchar),
     IsRated = cast((SELECT Count(1) From Ratings Where (Ratings.BookId=Books.BookId AND Ratings.UserId=@userId)) as nvarchar)
     From Books
     Order By Rating desc
     OFFSET 0 ROWS
     FETCH NEXT 3 ROWS ONLY;
RETURN 0
